/**
 * 
 */
package com.java.test.user.mapper;

import java.time.LocalDateTime;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.java.test.datacenter.dto.HaviorAllData;
import com.java.test.datacenter.dto.HaviorData;
import com.java.test.user.dto.BehaviorInfo;
import com.java.test.util.PageInfo;

/**
 * @author Administrator
 *
 */
@Component
public class BehaviorMapper {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public int selectBehavior(String game_account, String startTime, String endTime) {
		String sql = "select count(*) from member_behavior where game_account='" + game_account + "'";

		if (startTime != null && !"".equals(startTime)) {
			sql += " and oper_time>='" + startTime + "'";
		}

		if (endTime != null && !"".equals(endTime)) {
			sql += " and oper_time<'" + endTime + "'";
		}

		Integer i = jdbcTemplate.queryForObject(sql, Integer.class);

		return i == null ? null : i;

	}

	public List<BehaviorInfo> selectBehaviorList(String game_account, PageInfo<BehaviorInfo> page, String startTime,
			String endTime) {
		String sql = "select * from member_behavior where game_account='" + game_account + "'";

		if (startTime != null && !"".equals(startTime)) {
			sql += " and oper_time>='" + startTime + "'";
		}

		if (endTime != null && !"".equals(endTime)) {
			sql += " and oper_time<'" + endTime + "'";
		}

		int start = (page.getCurrentPage() - 1) * page.getPageCount();
		int limit = page.getPageCount();

		sql += "limit ?,?";
		List<BehaviorInfo> list = jdbcTemplate.query(sql, new BehaviorInfo(), start, limit);
		return list;
	}

	public int selectActiveUsercount(LocalDateTime startTime, LocalDateTime endTime) {

		String sql = "select count(*) from (select game_account from member_behavior where 1=1 ";

		String sql2 = " and behavior_type in(2,3,4,5,6,7) group by game_account) temp";

		String sqlitem = "";
		if (startTime != null) {
			sql += " and oper_time>='" + startTime + "'";
		}
		if (endTime != null) {
			sql += " and oper_time<='" + endTime + "'";
		}

		int count = jdbcTemplate.queryForObject(sql + sqlitem + sql2, Integer.class);

		return count;

	}

	/**
	 * 查询行为统计
	 */
	public HaviorAllData selectHaviorcount(String startTime, String endTime) {

		String sql1 = "select count(*) from member_behavior where behavior_type=1"; // 注册

		String sql2 = "select count(*) from member_behavior where behavior_type=2"; // 登陆

		String sql3 = "select count(*) from member_behavior where behavior_type=3"; // 存款

		String sql4 = "select count(*) from member_behavior where behavior_type=4"; // 提款

		String sql5 = "select count(*) from member_behavior where behavior_type=5"; // 参与游戏

		String sql6 = "select count(*) from member_behavior where behavior_type=6"; // 签到

		String sql7 = "select count(*) from member_behavior where behavior_type=7"; // 参与活动

		String sql8 = "select count(*) from member_behavior where behavior_type=8"; // 查看交易流水

		String sqlitem = "";
		boolean iswriteTime = false;
		if (startTime != null && !"".equals(startTime)) {
			sqlitem += " and oper_time>='" + startTime + "'";
			iswriteTime = true;
		}

		if (endTime != null && !"".equals(endTime)) {
			sqlitem += " and oper_time<='" + endTime + "'";
			iswriteTime = true;
		}

		if (!iswriteTime) {
			sqlitem += " and TO_DAYS(oper_time)=TO_DAYS(NOW())";
		}

		int count1 = jdbcTemplate.queryForObject(sql1 + sqlitem, Integer.class);
		int count2 = jdbcTemplate.queryForObject(sql2 + sqlitem, Integer.class);
		int count3 = jdbcTemplate.queryForObject(sql3 + sqlitem, Integer.class);
		int count4 = jdbcTemplate.queryForObject(sql4 + sqlitem, Integer.class);
		int count5 = jdbcTemplate.queryForObject(sql5 + sqlitem, Integer.class);
		int count6 = jdbcTemplate.queryForObject(sql6 + sqlitem, Integer.class);
		int count7 = jdbcTemplate.queryForObject(sql7 + sqlitem, Integer.class);
		int count8 = jdbcTemplate.queryForObject(sql8 + sqlitem, Integer.class);

		HaviorAllData info = new HaviorAllData();
		info.setCount1(count1);
		info.setCount2(count2);
		info.setCount3(count3);
		info.setCount4(count4);
		info.setCount5(count5);
		info.setCount6(count6);
		info.setCount7(count7);
		info.setCount8(count8);

		return info;

	}

	// 查询某一种行为统计图
	public List<HaviorData> selectHavior(String startTime, String endTime, int type) {
		String sql = "";
		String sqlitem = "";
		boolean iswriteTime = false;

		if (endTime == null || "".equals(endTime)) {
			iswriteTime = true;
		} else {
			if (startTime != null && !"".equals(startTime)) {
				sqlitem += " and oper_time>='" + startTime + "'";
			}

			if (endTime != null && !"".equals(endTime)) {
				sqlitem += " and oper_time<='" + endTime + "'";
			}
		}

		if (iswriteTime) {
			sql = "select hour(oper_time) as time,count(*) as count from member_behavior where behavior_type='" + type
					+ "'";
			sqlitem += " and TO_DAYS(oper_time)=TO_DAYS('"+startTime+"') group by hour(oper_time)";
		} else {
			sql = "select date(oper_time) as time,count(*) as count from member_behavior where behavior_type='" + type
					+ "'";
			sqlitem += " group by date(oper_time)";
		}

		List<HaviorData> list = jdbcTemplate.query(sql + sqlitem, new HaviorData());

		return list;
	}
	
	
	
	
	
	
	
	
	
	

}
